﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using Microsoft.Win32;
using Excel = Microsoft.Office.Interop.Excel;
using System.Data.OleDb;
using System.Data;
using SmartSpace.Core;
using System.Threading;
using System.IO;
using SmartSpace.Packaging;

namespace SsTool.Install
{
    /// <summary>
    /// Interaction logic for ImportUserFromExcel.xaml
    /// </summary>
    public partial class ImportUserFromExcel : Page
    {
        private string _filePath;

        public ImportUserFromExcel()
        {
            InitializeComponent();
        }

        private void _ctlBtnBroswer_Click(object sender, RoutedEventArgs e)
        {
            OpenFileDialog dlg = new OpenFileDialog();
            dlg.Filter = "excel(*.xlsx)|*.xlsx";
            dlg.Multiselect = false;
            if (dlg.ShowDialog() == true)
            {
                _ctlBtnBroswer.Content = dlg.FileName;
                _filePath = dlg.FileName;

                ExcelUtil util = new ExcelUtil(_filePath);
                _ctlListSheets.ItemsSource = util.GetSheets();
                util.Close();
            }
        }

        private void _ctlListSheets_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            string sheetName = e.AddedItems[0].ToString();

            ExcelUtil util = new ExcelUtil(_filePath);
            DataTable table = util.GetData(sheetName, 20);
            util.Close();

            List<string> columns = new List<string>();
            foreach (DataColumn item in table.Columns)
            {
                columns.Add(item.ColumnName);
            }

            _ctlListAccount.ItemsSource = columns;
            _ctlListName.ItemsSource = columns;
            _ctlListPhone.ItemsSource = columns;
            _ctlListEmail.ItemsSource = columns;
        }

        private void _ctlBtnImport_Click(object sender, RoutedEventArgs e)
        {
            string accountColumn = _ctlListAccount.SelectedItem.ToString();
            string nameColumn = _ctlListName.SelectedItem.ToString();
            string phoneColumn = _ctlListPhone.SelectedItem.ToString();
            string emailColumn = _ctlListEmail.SelectedItem.ToString();

            string sheetName = _ctlListSheets.SelectedItem.ToString();


            Thread thread = new Thread(new ParameterizedThreadStart(Importing));
            this.IsEnabled = false;
            _ctlOutput.Text = "开始导入";
            thread.Start(new string[]{accountColumn, nameColumn, phoneColumn, emailColumn, sheetName});
        }

        private void Importing(object p)
        {
            string[] args = (string[])p;

            ExcelUtil util = new ExcelUtil(_filePath);
            DataTable table = util.GetData(args[4]);
            util.Close();

            ICoreService coreService = App.Get<ICoreService>();

            for (int i = 0; i < table.Rows.Count; i++)
            {
                DataRow row = table.Rows[i];
                string account = Convert.ToString(row[args[0]]);
                string name = Convert.ToString(row[args[1]]);
                string phone = Convert.ToString(row[args[2]]);
                string email = Convert.ToString(row[args[3]]);

                if (!string.IsNullOrEmpty(account) && !string.IsNullOrEmpty(name))
                {
                    User user = new User();
                    user.Id = Guid.NewGuid();
                    user.Account = account;
                    user.Name = name;
                    user.Telphone = phone;
                    user.Email = email;
                    user.CreatedTime = DateTime.Now;
                    user.Password = "123456";

                    if (coreService.StoreUser(user))
                    {
                        Dispatcher.Invoke(
                            new Action<string, string>(Render),
                            account,
                            name);
                    }
                }
            }
            Dispatcher.Invoke(new Action(Finish));
        }

        private void Render(string account, string name)
        {
            _ctlOutput.Text += string.Format("导入{1}({0})成功!{2}", account, name, Environment.NewLine);
            _ctlOutput.ScrollToEnd();
        }

        private void Finish()
        {
            _ctlOutput.Text += "导入完成";
            _ctlOutput.ScrollToEnd();
            this.IsEnabled = true;
        }

        #region 导入到xml
        private void _ctlBtnImportToXml_Click(object sender, RoutedEventArgs e)
        {
            string accountColumn = _ctlListAccount.SelectedItem.ToString();
            string nameColumn = _ctlListName.SelectedItem.ToString();
            string phoneColumn = _ctlListPhone.SelectedItem.ToString();
            string emailColumn = _ctlListEmail.SelectedItem.ToString();

            string sheetName = _ctlListSheets.SelectedItem.ToString();


            Thread thread = new Thread(new ParameterizedThreadStart(ImportingToXml));
            this.IsEnabled = false;
            _ctlOutput.Text = "开始导出";
            thread.Start(new string[] { accountColumn, nameColumn, phoneColumn, emailColumn, sheetName });
        }

        private void ImportingToXml(object p)
        {
            string[] args = (string[])p;

            ExcelUtil util = new ExcelUtil(_filePath);
            DataTable table = util.GetData(args[4]);
            util.Close();

            List<User> users = new List<User>();
            for (int i = 0; i < table.Rows.Count; i++)
            {
                DataRow row = table.Rows[i];
                string account = Convert.ToString(row[args[0]]);
                string name = Convert.ToString(row[args[1]]);
                string phone = Convert.ToString(row[args[2]]);
                string email = Convert.ToString(row[args[3]]);

                if (!string.IsNullOrEmpty(account) && !string.IsNullOrEmpty(name))
                {
                    bool isExist = false;
                    foreach (var item in users)
                    {
                        if (item.Account == account)
                        {
                            isExist = true;
                            break;
                        }
                    }

                    if (!isExist)
                    {
                        User user = new User();
                        user.Id = Guid.NewGuid();
                        user.Account = account;
                        user.Name = name;
                        user.Telphone = phone;
                        user.Email = email;
                        user.CreatedTime = DateTime.Now;
                        user.Password = "123456";

                        users.Add(user);
                        Dispatcher.Invoke(
                                new Action<string, string>(Render),
                                account,
                                name);
                    }
                }
            }

            FileInfo fi = new FileInfo(_filePath);
            string xmlFileName = fi.Name.Replace(fi.Extension, string.Empty);
            string xmlFilePath = fi.Directory.FullName+"\\"+xmlFileName+".xml";
            XUserUtil.Save(users, xmlFilePath);
            Dispatcher.Invoke(new Action<string>(Finish2), xmlFilePath);
        }
        private void Finish2(string path)
        {
            _ctlOutput.Text += "导入完成:" + path;
            _ctlOutput.ScrollToEnd();
            this.IsEnabled = true;
        }
        #endregion
    }

    public class ExcelUtil
    {
        public static Dictionary<string, DataTable> s_mappingValues = new Dictionary<string, DataTable>();

        private Excel.Application _app;
        Excel.Workbook _workbooks;
        private string _path;
        public ExcelUtil(string path)
        {
            _path = path;
            Open();
        }

        private void Open()
        {
            _app = new Excel.Application();
            _app.Visible = false;
            object objOpt = System.Reflection.Missing.Value;
            _workbooks = (Excel.Workbook)_app.Workbooks.Open(_path, objOpt, false, objOpt, objOpt, objOpt, true, objOpt, objOpt, true, objOpt, objOpt, objOpt, objOpt, objOpt);

        }

        public List<string> GetSheets()
        {
            List<string> list = new List<string>();
            Excel.Sheets sheets = _workbooks.Worksheets;
            string sheetNams = string.Empty;
            foreach (Excel.Worksheet sheet in sheets)
            {
                list.Add(sheet.Name);
            }
            return list;
        }

        public DataTable GetData(string sheetName)
        {
            DataTable table = new DataTable();

            Excel.Worksheet sheet = null;
            for (int sheetIndex = 1; sheetIndex <= _workbooks.Worksheets.Count; sheetIndex++)
            {
                Excel.Worksheet worksheet = (Excel.Worksheet)_workbooks.Worksheets[sheetIndex];
                if (worksheet.Name == sheetName)
                {
                    sheet = worksheet;
                    break;
                }
            }

            if (sheet == null)
            {
                return null;
            }

            int totalColumn = sheet.UsedRange.Cells.Columns.Count;
            int totalRow = sheet.UsedRange.Cells.Rows.Count;

            if (totalRow < 0)
            {
                return null;
            }

            for (int col = 1; col <= totalColumn; col++)
            {
                Excel.Range header = sheet.UsedRange.Cells[1, col];
                try
                {
                    DataColumn dataColumn = new DataColumn(header.Text);
                    dataColumn.Unique = false;
                    table.Columns.Add(dataColumn);
                }
                catch
                {
                    DataColumn dataColumn = new DataColumn(header.Text + "_1");
                    dataColumn.Unique = false;
                    table.Columns.Add(dataColumn);
                }
            }

            for (int row = 2; row <= totalRow; row++)
            {
                DataRow dataRow = table.NewRow();
                for (int colIndex = 0; colIndex < table.Columns.Count; colIndex++)
                {
                    Excel.Range textRange = (Excel.Range)sheet.Cells[row, colIndex + 1];
                    dataRow[table.Columns[colIndex]] = textRange.Text;
                }
                table.Rows.Add(dataRow);
            }

            return table;
        }

        public DataTable GetData(string sheetName, int count)
        {
            DataTable table = new DataTable();

            Excel.Worksheet sheet = null;
            for (int sheetIndex = 1; sheetIndex <= _workbooks.Worksheets.Count; sheetIndex++)
            {
                Excel.Worksheet worksheet = (Excel.Worksheet)_workbooks.Worksheets[sheetIndex];
                if (worksheet.Name == sheetName)
                {
                    sheet = worksheet;
                    break;
                }
            }

            if (sheet == null)
            {
                return null;
            }

            int totalColumn = sheet.UsedRange.Cells.Columns.Count;
            int totalRow = sheet.UsedRange.Cells.Rows.Count;

            if (totalRow < 0)
            {
                return null;
            }

            for (int col = 1; col <= totalColumn; col++)
            {
                Excel.Range header = sheet.UsedRange.Cells[1, col];
                try
                {
                    DataColumn dataColumn = new DataColumn(header.Text);
                    dataColumn.Unique = false;
                    table.Columns.Add(dataColumn);
                }
                catch
                {
                    DataColumn dataColumn = new DataColumn(header.Text + "_1");
                    dataColumn.Unique = false;
                    table.Columns.Add(dataColumn);
                }
            }

            for (int row = 2; row <= totalRow && row <= count + 1; row++)
            {
                DataRow dataRow = table.NewRow();
                for (int colIndex = 0; colIndex < table.Columns.Count; colIndex++)
                {
                    Excel.Range textRange = (Excel.Range)sheet.Cells[row, colIndex + 1];
                    dataRow[table.Columns[colIndex]] = textRange.Text;
                }
                table.Rows.Add(dataRow);
            }

            return table;
        }

        public void Close()
        {
            if (_workbooks != null)
            {
                _workbooks.Close(false, Type.Missing, Type.Missing);
                _app.Quit();
            }
        }

        private bool Cmp(string dataTableColumnName, string xmlExcelColumnName)
        {
            int value = Convert.ToInt32(dataTableColumnName.Replace("F", string.Empty));
            int xmlValue = 0;
            for (int index = 0; index < xmlExcelColumnName.Length; index++)
            {
                int c = xmlExcelColumnName[index] - 'A' + 1;

                xmlValue += c * (int)Math.Pow(26, xmlExcelColumnName.Length - index - 1);
            }

            if (value == xmlValue)
            {
                return true;
            }
            return false;
        }

        public static bool ExportExcel(DataTable table, string excelFileName)
        {
            object objOpt = System.Reflection.Missing.Value;
            Excel.Application excel = new Excel.Application();
            try
            {
                excel.Visible = false;
                Excel._Workbook wkb = excel.Workbooks.Add(objOpt);
                Excel._Worksheet wks = (Excel._Worksheet)wkb.ActiveSheet;

                wks.Visible = Excel.XlSheetVisibility.xlSheetVisible;

                int rowIndex = 1;
                int colIndex = 0;

                foreach (DataColumn col in table.Columns)
                {
                    colIndex++;
                    excel.Cells[1, colIndex] = col.ColumnName;
                }

                foreach (DataRow row in table.Rows)
                {
                    rowIndex++;
                    colIndex = 0;
                    foreach (DataColumn col in table.Columns)
                    {
                        colIndex++;
                        excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
                    }
                }
                //excel.Sheets[0] = "sss";
                wkb.SaveAs(excelFileName, objOpt, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
                wkb.Close(false, objOpt, objOpt);
            }
            catch
            {
                return false;
            }
            finally
            {
                excel.Quit();
            }

            return true;
        }
    }

    public class AA24
    {
        /// <summary>
        /// 27->AA
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        public static string ToAA(int value)
        {
            string AA = string.Empty;
            int d = value / 26;
            int remainder = value % 26;

            if (d == 1 && remainder == 0)
            {
                AA = ToA(26) + AA;
                d = 0;
            }
            else
            {
                AA = ToA(remainder) + AA;
            }

            while (d > 0)
            {
                remainder = d % 26;
                d = d / 26;
                AA = ToA(remainder) + AA;
            }
            return AA;
        }

        /// <summary>
        /// 27->F27
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        public static string ToF27(int value)
        {
            return string.Format("F{0}", value);
        }

        /// <summary>
        /// AA->27
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        public static int FromAA(string value)
        {
            int V = 0;

            for (int index = 0; index < value.Length; index++)
            {
                int v = FromA(value[index]);
                V += v * (int)Math.Pow(v, index);
            }
            return V;
        }

        /// <summary>
        /// F27->27
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        public static int FromF27(string value)
        {
            return Convert.ToInt32(value.Replace("F", string.Empty));
        }

        private static string ToA(int value)
        {
            if (value <= 0)
            {
                return string.Empty;
            }
            return new string((char)('A' + (value - 1)), 1);
        }

        private static int FromA(char value)
        {
            return (int)value - (char)'A' + 1;
        }
    }
}
